-- 系统所有课程的目录路径和模块资源路径
SELECT
	itm_id,
	itm_title as "课程名称",
	itm_desc as "简介",
	rcn_res_id_content as "关联内容资源",
	res_title as "资源名称",
	ITM_CREATE_TIMESTAMP,
	ITM_PUBLISH_TIMESTAMP,
	RES_SRC_LINK,
	'resource/' || res_id || '/'||RES_SRC_LINK as "资源路径",
	RES_SRC_type,
	a1.tnd_id,
	a1.TND_TITLE as "课程标题" ,
	tempa.fulltitle as "课程路径"
FROM
	aeItem 
INNER JOIN tcTrainingCenter ON tcr_id=itm_tcr_id
INNER JOIN RegUser usr2 ON usr2.usr_id=itm_upd_usr_id 
INNER JOIN Entity ent2 ON ent2.ent_id=usr2.usr_ent_id 
INNER JOIN Course  On cos_itm_id=itm_id
INNER JOIN ResourceContent  On cos_res_id = rcn_res_id
INNER JOIN Module On rcn_res_id_content = mod_res_id
INNER JOIN Resources On rcn_res_id_content=res_id
INNER JOIN aeTreeNode a1 On a1.TND_ITM_ID=itm_id
INNER JOIN (SELECT a2.TND_TITLE,a2.tnd_id,a2.TND_PARENT_TND_ID,sys_connect_by_path(a2.TND_TITLE,'/') as fulltitle
FROM aeTreeNode a2
START WITH a2.TND_PARENT_TND_ID is null
CONNECT BY PRIOR a2.tnd_id =a2.TND_PARENT_TND_ID
) tempa on tempa.TND_ID=a1.TND_ID
-- WHERE itm_life_status is null and itm_type='SELFSTUDY' and itm_run_ind = 0
ORDER BY ITM_PUBLISH_TIMESTAMP desc,itm_id desc
